#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from lxml import etree
import openpyxl
import time


def timer(tracer=False):
    """计时装饰器"""
    def _timer(func):
        alltime = 0
        def wrapper(*args, **kwargs):
            if tracer:
                start = time.clock()
                result = func(*args, **kwargs)
                elapsed = time.clock() - start
                nonlocal alltime
                alltime += elapsed
                print('%s 耗时 %.5f 秒, 总计耗时 %.5f 秒' % (func.__name__, elapsed, alltime))
                return result
            else:
                return func(*args, **kwargs)
        return wrapper
    return _timer



@timer()
def make_dict(key_list, value_list):
    """
    根据key列表和value列表生成字典，字典中的value不能是'N/A'，字典中的key,value对不能同时为空字符串""
    :param key_list: key 列表
    :param value_list: value 列表
    :return: value不是'N/A'的字典
    """
    dic = {}
    for index, value in enumerate(value_list):
        # 如果有属性名，但是属性值不填，则生成的xml中的属性值设为空字符串""
        if key_list[index] is not None and value is None:
            dic[key_list[index]] = ""
        # 如果有属性名，但是属性值不为'N/A'，才生成相应的xml属性
        elif key_list[index] is not None and value != 'N/A':
            dic[key_list[index]] = str(value)
    return dic


@timer()
def load_excel(excel_file_path):
    """
    遍历excel表格的sheet页，再遍历每一行，如果任意行的第一个单元格的内容是'>>XPath'，则说明这一行是属性名称，
    如果第一个单元格的内容包含'>>'字符且内容不是'>>XPath'，则说明这一行是属性值。
    :param excel_file_path: excel表格的文件路径
    :return: 迭代元组，包括表格的sheet名，所在行，属性名和属性值对应的字典
    """
    wb = openpyxl.load_workbook(excel_file_path)
    ws_list = wb.sheetnames
    attr_list, val_list = [], []
    for ws_name in ws_list:
        ws = wb[ws_name]
        for _current_row in range(1, ws.max_row + 1):
            tag = ws.cell(_current_row, 1).value
            if tag == '>>XPath':
                attr_list = []
                for _current_col in range(1, ws.max_column + 1):
                    attr_list.append(ws.cell(_current_row, _current_col).value)
            elif tag and '>>' in tag:
                val_list = []
                for _current_col in range(1, ws.max_column + 1):
                    val_list.append(ws.cell(_current_row, _current_col).value)
                dic = make_dict(attr_list, val_list)
                yield dic


@timer()
def append_xml(f_xpath, f_element, attr_dict):
    """
    根据load_excel产生的可迭代元组，追加xml元素
    :param f_xpath: 依赖的xml父节点的xpath
    :param f_element: 依赖的xml父节点
    :param attr_dict: load_excel产生的可迭代元组
    :return: 追加的xml元素
    """
    c_xpath = '/' + attr_dict['>>XPath'][2:]
    diff_xpath_list = c_xpath.replace(f_xpath, '').split('/')[1:]
    attr_dict.pop('>>XPath')
    _f_element = f_element
    for diff_xpath_sec in diff_xpath_list:
        _f_element = etree.SubElement(_f_element, diff_xpath_sec)
    c_element = _f_element
    for attr, val in attr_dict.items():
        c_element.set(attr, val)
    return c_element


@timer()
def search_xpath(element_tree, xpath):
    """
    循环搜索目标xpath所在xml元素树中依附的父节点
    :param element_tree: xml元素树
    :param xpath: 目标xpath
    :return: 依附的父节点
    """
    xpath_li = xpath.split('/')
    for i in range(len(xpath_li)):
        _xpath = '/' + '/'.join(xpath_li[:-i])
        _element = element_tree.xpath(_xpath)
        if _element:
            return _xpath, _element[-1]


@timer()
def make_xml(excel_file_path):
    root_name = load_excel(excel_file_path).__next__()['>>XPath'][2:].split('/')[0]
    root = etree.Element(root_name)
    gen = load_excel(excel_file_path)
    for attr_dict in gen:
        xpath = attr_dict['>>XPath'][2:]
        f_xpath, f_element = search_xpath(root, xpath)
        append_xml(f_xpath, f_element, attr_dict)
    xml_doc = etree.ElementTree(root)
    return xml_doc


def write_xml_file(xml_doc, xml_file_path):
    xml_doc.write(xml_file_path, encoding='UTF-8', xml_declaration=True, pretty_print=True)


if __name__ == '__main__':
    # Excel 文件地址
    excel_path = r'C:\Users\xiaoyao\Desktop\Objects_Data.xlsx'
    # XML 文件地址
    xml_path = r'C:\Users\xiaoyao\Desktop\Objects_Data.xml'
    doc = make_xml(excel_path)
    # xml_string = etree.tostring(doc, encoding='UTF-8', xml_declaration=True, pretty_print=True).decode("utf-8")
    # print(xml_string)
    write_xml_file(doc, xml_path)
